<?php
namespace App\Models\Scripts;

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

class v84789f37233226e3058a5a57b03e67be extends Sysupdate
{
    protected $version_id ='salebonus';

    private $returnData = array('code'=>0,'message'=>'执行成功');

    public function upgrade()
    {
        $r_data = $this->_step_1();
        if(!$r_data){
            return $r_data;
        }
        $tmp_data = $r_data['data'];
        $r_data = $this->_step_2();
        if(!$r_data){
            return $r_data;
        }
        $r_data = $this->_step_3();
        if(!$r_data){
            return $r_data;
        }
        $r_data = $this->_step_4($tmp_data);
        if(!$r_data){
            return $r_data;
        }
        return $this->returnData;
    }

    private function _step_1()
    {
        $res = DB::table('salebonusrule as a')
            ->select('a.id','a.rule_name','a.rule_type','b.cost','b.pre_bonus','b.after_bonus','b.after_first_bonus','a.created_at','a.updated_at')
            ->leftJoin('salebonusinfo as b','a.id'. '=' .'b.sbr_id')
            ->groupBy('b.sbr_id')
            ->get();
        if(!$res){
            $this->returnData['code'] = 101;
            $this->returnData['message'] = '筛选salebonusrule数据失败';
            return $this->returnData;
        }
        $res = json_decode(json_encode($res),true);
        if(!is_array($res) || count($res)<1){
            $this->returnData['code'] = 102;
            $this->returnData['message'] = 'salebonusrule数据为空';
            return $this->returnData;
        }
        $this->returnData['data'] = $res;
        return $this->returnData;
    }

    private function _step_2()
    {
        $sql = "DROP TABLE IF EXISTS `salebonusrule`;".
            "DROP TABLE IF EXISTS `salebonusinfo`;";
        DB::statement($sql);
        return $this->returnData;
    }

    private function _step_3()
    {
        $sql = "CREATE TABLE `salebonusrule` (".
            "  `id` int(11) NOT NULL AUTO_INCREMENT,".
            "  `rule_name` varchar(50) DEFAULT NULL COMMENT '规则名称',".
            "  `rule_type` tinyint(1) DEFAULT NULL COMMENT '提成规则 0 比例/1 固定',".
            "  `cost` int(6) NOT NULL DEFAULT '0' COMMENT '成本',".
            "  `pre_bonus` int(6) NOT NULL DEFAULT '0' COMMENT '售前比例或固定金额',".
            "  `after_bonus` int(6) NOT NULL DEFAULT '0' COMMENT '售后比例或固定金额',".
            "  `after_first_bonus` int(6) DEFAULT '0' COMMENT '售后首笔提成比例',".
            "  `created_at` timestamp NULL DEFAULT NULL,".
            "  `updated_at` timestamp NULL DEFAULT NULL,".
            "  PRIMARY KEY (`id`)".
            ") ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8;";
        DB::statement($sql);
        return $this->returnData;
    }

    private function _step_4($data)
    {
        try {
            //检查有数据
            $res = DB::table('salebonusrule')->count();
            if($res>0){
                $this->returnData['code'] = 103;
                $this->returnData['message'] = $this->version_id.' exist';
                return $this->returnData;
            }
        } catch(\Illuminate\Database\QueryException $e) {
            $errorInfo = json_decode(json_encode($e->errorInfo),true);
            if($errorInfo[0]=='42S02'){
                $this->returnData['code'] = 104;
                $this->returnData['message'] = 'step 3 create table salebonusrule fail.';
                return $this->returnData;
            }
        }
        /* update code start*/
        $res = DB::table('salebonusrule')->insert($data);
        if(!$res){
            $this->returnData['code'] = 99;
            $this->returnData['message'] = $this->version_id.'更新失败';
            return $this->returnData;
        }
        return $this->returnData;
    }
}
